package dataBase;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class tempBase {
	final static String user = "root";
	final static String password = "000000";
	final static String url = "jdbc:mysql://127.0.0.1:3306/temp";
	
	final static String tempTable = "temp";
	final static int tColumnNumber = 3;
	final static String tTableSt = "courseID,studentID,isDone";
	
	final static String sTable = "student";
	final static int sColumnNumber = 3;
	final static String sTableSt = "studentID,credit,score";
	
	Connection con;
	Statement stmt;
	
	public tempBase(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con =  (Connection) DriverManager.getConnection(url, user, password);
			stmt = (Statement) con.createStatement();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public boolean addStudentInfo(String id,String credit,String score){
		boolean result = true;
		String sqlstr = "insert into student (id,credit,score) value('"+id+"','"+credit+"','"
				+score+"')";
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;
	}
	
	public boolean setScore(String id,String credit){
		boolean result = true;
		String sqlstr = "update student set credit = '"+credit+"' where id = '"+id+"'";
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;
	}
	
	public int getCredit(String id){
		int result = 0;
		String sqlstr = "select * from student where id = '"+id+"'";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			rs.next();
			result = rs.getInt(2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean addTemp(String courseID,String studentID){
		boolean result = true;
		String sqlstr = "insert into temp (courseID,studentID) value('"+courseID+"','"+studentID+"')";
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;
	}
	
	public boolean setDone(String courseID,String studentID){
		boolean result = true;
		String sqlstr = "update temp set isDone = '1' where courseID = '"+courseID
				+"' and studentID = '"+studentID+"'";
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;
	}
	
	public boolean setDoneByCid(String courseID){
		boolean result = true;
		String sqlstr = "update temp set isDone = '"+1+"' where courseID = '"+courseID+"'";
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;
	}
	
	public boolean setDoneBySid(String studentID){
		boolean result = true;
		String sqlstr = "update temp set isDone = '"+1+"' where studentID = '"+studentID+"'";
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;	
		
	}
	
	public ArrayList<String[]> queryNotDone(){
		ArrayList<String[]> result = new ArrayList<String[]>();
		String sqlstr = "select * from temp where isDone = 0";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			while(rs.next()){
				String[] info = new String[2];
				info[0] = rs.getString(1);
				info[1] = rs.getString(2);
				result.add(info);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public ArrayList<String[]> queryTemp(String StudentID){
		ArrayList<String[]> result = new ArrayList<String[]>();
		String sqlstr = "select * from temp where isDone = 0 and studentID = '"+StudentID+"'";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			while(rs.next()){
				String[] info = new String[2];
				info[0] = rs.getString(1);
				info[1] = rs.getString(2);
				result.add(info);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	public ArrayList<String> queryStudent(){
		ArrayList<String> result = new ArrayList<String>();
		String sqlstr = "select * from student where isDone = 0 order by score DESC , addTime";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			while(rs.next()){
				String info = rs.getString(1);
				result.add(info);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	} 
	
	public boolean isAdded(String studentID){
		boolean result = false;
		String sqlstr = "select * from student where id = '"+studentID+"'";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			if(rs.next())
				result = true;			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean setStudentDone(String id){
		boolean result = true;
		String sqlstr = "update student set isDone = 1 where id = "+id;
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;
	}
	
	public boolean deleteStudent(String id){
		boolean result = true;
		String sqlstr = "delete from student where id = "+id;
		try {
			stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			result = false;
		}
		return result;
	}
}
